package com.ying.dao.order;

import com.ying.po.order.Express;
import com.ying.po.order.Order;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;

public interface ExpressRepository extends JpaRepository<Express, Integer> {

    @Query(value = "select * from t_express where IS_DELETE=0", nativeQuery = true)
    public Page<Express> getExpressPageIsNotDeleted(Pageable pageable);

    @Query(value = "select * from t_express where IS_DELETE=0 and user_id=:userId", nativeQuery = true)
    public List<Express> findAllIsNotDelete(@Param("userId") Integer userId);

    @Query(value = "select * from t_express where is_delete=0 and id=?1", nativeQuery = true)
    public Optional<Express> findByIdAndIsNotDelete(Integer id);

    @Query(value = "select * from t_express where is_delete=0 and target=:t and user_id=:u", nativeQuery = true)
    List<Express> findByTarget(@Param("t") String t, @Param("u") Integer u);

    @Query(value = "select * from t_express where is_delete=0 and name=:n and user_id=:u", nativeQuery = true)
    List<Express> findByName(@Param("n")String n, @Param("u")Integer u);

    @Query(value = "select * from t_express where is_delete=0 and date like :y% and user_id=:u", nativeQuery = true)
    List<Express> findByYear(@Param("y")String y, @Param("u")Integer u);

    @Query(value = "select * from t_express where is_delete=0 and target=:t and name=:n and user_id=:u", nativeQuery = true)
    List<Express> findByTargetAndName(@Param("t")String t, @Param("n")String n, @Param("u")Integer u);

    @Query(value = "select * from t_express where is_delete=0 and target=:t and date like :y% and user_id=:u", nativeQuery = true)
    List<Express> findByTargetAndYear(@Param("t")String t, @Param("y")String y, @Param("u")Integer u);

    @Query(value = "select * from t_express where is_delete=0 and name=:n and date like :y% and user_id=:u", nativeQuery = true)
    List<Express> findByNameAndYear(@Param("n")String n, @Param("y")String y, @Param("u")Integer u);

    @Query(value = "select * from t_express where is_delete=0 and target=:t and name=:n and date like :y% and user_id=:u", nativeQuery = true)
    List<Express> findByTargetAndNameAndYear(@Param("n")String n, @Param("t")String t, @Param("y")String y, @Param("u")Integer u);

//    @Query(value = "select * from t_express where is_delete=0 and user_id=:u  and name=(select name from t_express where id=:e)", nativeQuery = true)
//    List<Express> findIdsByName(@Param("e") Integer e, @Param("u") Integer u);

    @Query(value = "select id from t_express where is_delete=0 and user_id=:u  and name=(select name from t_express where id=:e)", nativeQuery = true)
    List<Integer> findIdsByName(@Param("e") Integer e, @Param("u") Integer u);

    @Query(value = "select id from t_express where is_delete=0 and user_id=:u  and name=:n and weight=:w and origin=:o and target=:t", nativeQuery = true)
    Integer findByNameWeightTargetOrigin(@Param("n") String expressName, @Param("w") Float weight, @Param("o")String origin, @Param("t") String target, @Param("u") Integer userId);
}
